package grsu.epam.airline.DAOImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import grsu.epam.airline.DAO.IAirportDAO;
import grsu.epam.airline.model.Airport;

public class AirportDAOImpl implements IAirportDAO {
	private static final String GET_ALL_AIRPORTS_QUERY = "select airport_id, airport_name from airports";
	private static final String UPDATE_AIRPORT = "update airports set airport_name=? where airport_id=?";
	private static final String ADD_AIRPORT = "insert into airports (airport_name) values (?)";
	private static final String GET_AIRPORT_BY_ID_QUERY = "select airport_id,airport_name from airports where airport_id=?";
	private static final String DELETE_AIRPORT = "DELETE FROM airports WHERE airport_id=?";
	private Connection conn;

	public AirportDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public void addAirport(Airport airport) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(ADD_AIRPORT);
			stmt.setString(1, airport.getAirportName());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public void updateAirport(Airport airport) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(UPDATE_AIRPORT);
			stmt.setString(1, airport.getAirportName());
			stmt.setInt(2, airport.getAirportId());
			stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public Airport getAirportById(int airportId) throws SQLException {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Airport airport = null;
		try {
			stmt = conn.prepareStatement(GET_AIRPORT_BY_ID_QUERY);
			stmt.setInt(1, airportId);
			rs = stmt.executeQuery();
			while (rs.next()) {
				airport = new Airport(rs.getInt("airport_id"),
						rs.getString("airport_name"));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return airport;
	}

	@Override
	public void deleteAirport(int airportId) throws SQLException {
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(DELETE_AIRPORT);
			stmt.setInt(1, airportId);
			stmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
	}

	@Override
	public List<Airport> getAllAirport() throws SQLException {
		List<Airport> airports = new ArrayList<Airport>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.prepareStatement(GET_ALL_AIRPORTS_QUERY);
			rs = stmt.executeQuery();
			while (rs.next()) {
				airports.add(new Airport(rs.getInt("airport_id"), rs
						.getString("airport_name")));
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception ignore) {
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception ignore) {
				}
			}
		}
		return airports;
	}

	@Override
	public Connection getConnection() {
		return this.conn;
	}
}
